﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using OnlineManagement.IDAL;
using OnlineManagement.DBUtility;//Please add references
namespace OnlineManagement.SQLServerDAL.Dal
{
	/// <summary>
	/// 数据访问类:Sow
	/// </summary>
	public partial class Sow:ISow
	{
		public Sow()
		{}
		#region  BasicMethod

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(Guid SowID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from Sow");
			strSql.Append(" where SowID=@SowID ");
			SqlParameter[] parameters = {
					new SqlParameter("@SowID", SqlDbType.UniqueIdentifier,16)			};
			parameters[0].Value = SowID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}

        /// <summary>
        /// add sow using Procedure
        /// </summary>
        /// <param name="model"></param>
        /// <param name="returnValue"></param>
        /// <returns></returns>
        public bool Add(OnlineManagement.Model.Sow model, out string returnValue, out string sowIdStr) {
            returnValue = "";
            sowIdStr = string.Empty;
            int rowsAffected;
            if(model==null){
                return false;
            }
            string procName = Common.PubProcedures.SowAddproc;
            SqlParameter[] parameters = {
					new SqlParameter("@HoggeryID", SqlDbType.Int,4),
                    new SqlParameter("@BitID", SqlDbType.NVarChar,20),
					new SqlParameter("@PigPenID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@TypeID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@SourceID", SqlDbType.TinyInt,1),
                    new SqlParameter("@DateJoinFarm", SqlDbType.Date,3),
					new SqlParameter("@Brithdary", SqlDbType.Date,3),
					new SqlParameter("@HybridizationCount", SqlDbType.TinyInt,1),
					new SqlParameter("@PropagateCount", SqlDbType.TinyInt,1),
					new SqlParameter("@PropagateStateID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@Comment", SqlDbType.Text),

                    new SqlParameter("@rowsAffected",SqlDbType.TinyInt,1),
                    new SqlParameter("@msg",SqlDbType.NVarChar,200),
                    new SqlParameter("@sowId",SqlDbType.UniqueIdentifier,16)
                                        };
            parameters[0].Value = model.HoggeryID;
            parameters[1].Value = model.BitID;
            parameters[2].Value = model.PigPenID;
            parameters[3].Value = model.TypeID;
            parameters[4].Value = model.SourceID;
            parameters[5].Value = model.DateJoinFarm;
            parameters[6].Value = model.Brithdary;
            parameters[7].Value = model.HybridizationCount;
            parameters[8].Value = model.PropagateCount;
            parameters[9].Value = model.PropagateStateID;
            parameters[10].Value = model.Comment;

            parameters[11].Direction = ParameterDirection.Output;
            parameters[12].Direction = ParameterDirection.Output;
            parameters[13].Direction = ParameterDirection.Output;
            try
            {
                DbHelperSQL.RunProcedure(procName, parameters, out rowsAffected, out returnValue,out sowIdStr);
                return rowsAffected > 0 ? true : false;
            }catch(Exception ex){
                throw new Exception("数据库连接发生错误,请稍后再试。");
            }
        }

        public bool Update(OnlineManagement.Model.Sow model, out string message) {
            message = string.Empty;
            if(model==null){
                return false;
            }
            SqlParameter[] parameters = {new SqlParameter("@SowId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@ChangedPigPenId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@HybridizationCount", SqlDbType.TinyInt,1),
					new SqlParameter("@PropagateCount", SqlDbType.TinyInt,1),
					new SqlParameter("@PropagateStateID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@Comment", SqlDbType.Text),
                    new SqlParameter("@rowsAffected",SqlDbType.TinyInt,1),
                    new SqlParameter("@msg",SqlDbType.NVarChar,200)
                                        };
            parameters[0].Value = model.SowID;
            parameters[1].Value = model.PigPenID;
            parameters[2].Value = model.HybridizationCount;
            parameters[3].Value = model.PropagateCount;
            parameters[4].Value = model.PropagateStateID;
            parameters[5].Value = model.Comment;
            parameters[6].Direction = ParameterDirection.Output;
            parameters[7].Direction = ParameterDirection.Output;
            int rowAffectd;
            DbHelperSQL.RunProcedure(OnlineManagement.Common.PubProcedures.SowUpdateProce, parameters, out rowAffectd, out message);
            return rowAffectd>0?true:false;
        }

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(Guid SowID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from Sow ");
			strSql.Append(" where SowID=@SowID ");
			SqlParameter[] parameters = {
					new SqlParameter("@SowID", SqlDbType.UniqueIdentifier,16)			};
			parameters[0].Value = SowID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

        public bool Delete(Guid sowId, out string message) {
            string procName = OnlineManagement.Common.PubProcedures.SowDeletedProce;
            SqlParameter[] parameters = { 
                                            new SqlParameter("@SowID", SqlDbType.UniqueIdentifier, 16),
                                            new SqlParameter("@rowsAffected",SqlDbType.TinyInt,1),
                                            new SqlParameter("@msg",SqlDbType.NVarChar,200)
                                        };
            parameters[0].Value = sowId;
            parameters[1].Direction = ParameterDirection.Output;
            parameters[2].Direction = ParameterDirection.Output;
            message = string.Empty;
            int rowsAffected;
            DbHelperSQL.RunProcedure(procName, parameters, out rowsAffected,out message);
            return rowsAffected>0?true:false;
        }
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string SowIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from Sow ");
			strSql.Append(" where SowID in ("+SowIDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public OnlineManagement.Model.Sow GetModel(Guid SowID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 SowID,HoggeryID,PigPenID,BitID,TypeID,SourceID,Brithdary,Wight,DateJoinFarm,WeedOut,InbreedingDegree,IsInFarm,NippleNo,HybridizationCount,PregnantCount,FanQingCount,NonpregnantCount,AbortionCount,PropagateCount,PropagateStateID,Comment from Sow ");
			strSql.Append(" where SowID=@SowID ");
			SqlParameter[] parameters = {
					new SqlParameter("@SowID", SqlDbType.UniqueIdentifier,16)			};
			parameters[0].Value = SowID;

			OnlineManagement.Model.Sow model=new OnlineManagement.Model.Sow();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public OnlineManagement.Model.Sow DataRowToModel(DataRow row)
		{
			OnlineManagement.Model.Sow model=new OnlineManagement.Model.Sow();
			if (row != null)
			{
				if(row["SowID"]!=null && row["SowID"].ToString()!="")
				{
					model.SowID= new Guid(row["SowID"].ToString());
				}
				if(row["HoggeryID"]!=null && row["HoggeryID"].ToString()!="")
				{
					model.HoggeryID=int.Parse(row["HoggeryID"].ToString());
				}
				if(row["PigPenID"]!=null && row["PigPenID"].ToString()!="")
				{
					model.PigPenID= new Guid(row["PigPenID"].ToString());
				}
				if(row["BitID"]!=null)
				{
					model.BitID=row["BitID"].ToString();
				}
				if(row["TypeID"]!=null && row["TypeID"].ToString()!="")
				{
					model.TypeID= new Guid(row["TypeID"].ToString());
				}
				if(row["SourceID"]!=null && row["SourceID"].ToString()!="")
				{
					model.SourceID=int.Parse(row["SourceID"].ToString());
				}
				if(row["Brithdary"]!=null && row["Brithdary"].ToString()!="")
				{
					model.Brithdary=DateTime.Parse(row["Brithdary"].ToString());
				}
				if(row["Wight"]!=null && row["Wight"].ToString()!="")
				{
					model.Wight=decimal.Parse(row["Wight"].ToString());
				}
				if(row["DateJoinFarm"]!=null && row["DateJoinFarm"].ToString()!="")
				{
					model.DateJoinFarm=DateTime.Parse(row["DateJoinFarm"].ToString());
				}
				if(row["WeedOut"]!=null && row["WeedOut"].ToString()!="")
				{
					model.WeedOut=DateTime.Parse(row["WeedOut"].ToString());
				}
				if(row["InbreedingDegree"]!=null)
				{
					model.InbreedingDegree=row["InbreedingDegree"].ToString();
				}
				if(row["IsInFarm"]!=null && row["IsInFarm"].ToString()!="")
				{
					if((row["IsInFarm"].ToString()=="1")||(row["IsInFarm"].ToString().ToLower()=="true"))
					{
						model.IsInFarm=true;
					}
					else
					{
						model.IsInFarm=false;
					}
				}
				if(row["NippleNo"]!=null && row["NippleNo"].ToString()!="")
				{
					model.NippleNo=int.Parse(row["NippleNo"].ToString());
				}
				if(row["HybridizationCount"]!=null && row["HybridizationCount"].ToString()!="")
				{
					model.HybridizationCount=int.Parse(row["HybridizationCount"].ToString());
				}
				if(row["PregnantCount"]!=null && row["PregnantCount"].ToString()!="")
				{
					model.PregnantCount=int.Parse(row["PregnantCount"].ToString());
				}
				if(row["FanQingCount"]!=null && row["FanQingCount"].ToString()!="")
				{
					model.FanQingCount=int.Parse(row["FanQingCount"].ToString());
				}
				if(row["NonpregnantCount"]!=null && row["NonpregnantCount"].ToString()!="")
				{
					model.NonpregnantCount=int.Parse(row["NonpregnantCount"].ToString());
				}
				if(row["AbortionCount"]!=null && row["AbortionCount"].ToString()!="")
				{
					model.AbortionCount=int.Parse(row["AbortionCount"].ToString());
				}
				if(row["PropagateCount"]!=null && row["PropagateCount"].ToString()!="")
				{
					model.PropagateCount=int.Parse(row["PropagateCount"].ToString());
				}
				if(row["PropagateStateID"]!=null && row["PropagateStateID"].ToString()!="")
				{
					model.PropagateStateID= new Guid(row["PropagateStateID"].ToString());
				}
				if(row["Comment"]!=null)
				{
					model.Comment=row["Comment"].ToString();
				}
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select SowID,HoggeryID,PigPenID,BitID,TypeID,SourceID,Brithdary,Wight,DateJoinFarm,WeedOut,InbreedingDegree,IsInFarm,NippleNo,HybridizationCount,PregnantCount,FanQingCount,NonpregnantCount,AbortionCount,PropagateCount,PropagateStateID,Comment ");
			strSql.Append(" FROM Sow ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" SowID,HoggeryID,PigPenID,BitID,TypeID,SourceID,Brithdary,Wight,DateJoinFarm,WeedOut,InbreedingDegree,IsInFarm,NippleNo,HybridizationCount,PregnantCount,FanQingCount,NonpregnantCount,AbortionCount,PropagateCount,PropagateStateID,Comment ");
			strSql.Append(" FROM Sow ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM Sow ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}

        /// <summary>
        /// get sows from procdure
        /// </summary>
        /// <param name="sowId"></param>
        /// <param name="currentPage"></param>
        /// <returns></returns>
        public DataSet GetSowsByProc(int hoggeryId,int currentPage) {
            string procName = OnlineManagement.Common.PubProcedures.GetSowsByHoggeryIDProce;
            SqlParameter[] pars = { new SqlParameter("@HoggeryID", SqlDbType.Int, 4),new SqlParameter("@pageIndex",SqlDbType.Int,4) };
            pars[0].Value = hoggeryId;
            pars[1].Value = currentPage;
            DataSet ds=  DbHelperSQL.RunProcedure(procName, pars);
            return ds;

        }
      

        public DataSet GetHybridizationSow(int hoggeryId) {
            string procName = OnlineManagement.Common.PubProcedures.GetHybridizationSowProc;
            SqlParameter[] pars = { new SqlParameter("@HoggeryID", SqlDbType.Int, 4) };
            pars[0].Value = hoggeryId;
            DataSet ds = DbHelperSQL.RunProcedure(procName, pars);
            return ds;
        }
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.SowID desc");
			}
			strSql.Append(")AS Row, T.*  from Sow T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}
        

		#endregion  BasicMethod


		#region  ExtensionMethod

        /// <summary>
        /// get sows info for SowSetting page
        /// </summary>
        /// <param name="hoggeryId"></param>
        /// <returns></returns>
        public DataSet GetSowSettingsInfo(int hoggeryId)
        {
            try
            {
                string procName = OnlineManagement.Common.PubProcedures.GetSowSettingsInfoProce;
                SqlParameter[] paras = { new SqlParameter("@hoggeryId", SqlDbType.Int, 4) };
                paras[0].Value = hoggeryId;
                DataSet ds = DbHelperSQL.RunProcedure(procName, paras);
                return ds;
            }catch(Exception ex){
                throw new Exception("数据库发生错误.");
            }
        }
        public DataSet GetSowActions()
        {
            try {
                string queryStr = "select PropagateStateOrder,PropagateStateName, ActionOrder,ActionName,UCPath from SowActions";
                System.Data.DataSet ds= DbHelperSQL.Query(queryStr);
                return ds;
            }catch(Exception ex){
                throw ex;
            }
        }

        public List<Model.SowOperations> GetSowOperations() {
            try {
                string queryStr = "SELECT PropagateStateOrder,PropagateStateName,ActionOrder,ActionName,UCPath FROM SowActions";
                SqlDataReader reader = DbHelperSQL.ExecuteReader(queryStr);
                List<Model.SowOperations> operations = new List<Model.SowOperations>();
                int order;
                int actionOrder;
                while(reader.Read()){
                    Model.SowOperations so = new Model.SowOperations();
                    int.TryParse(reader["PropagateStateOrder"].ToString(), out order);
                    so.SowPropagateStateOrder = order;
                    so.PropagateStateName = reader["PropagateStateName"].ToString();
                    int.TryParse(reader["ActionOrder"].ToString(), out actionOrder);
                    so.ActionOrder = actionOrder;

                }
                return operations;
            }catch(Exception ex){
                throw ex;
            }
        }
       public DataSet GetSowDataPaging(int hoggeryId, int pageSize, int currentPage, string whereStr, string orderByStr, out int rCount) 
       {
           
           rCount = 0;
           string procName = OnlineManagement.Common.PubProcedures.GetSowsByPageProce;
           SqlParameter[] paras = { 
                                      new SqlParameter("@HoggeryID", SqlDbType.Int, 4),
                                      new SqlParameter("@Where",SqlDbType.NVarChar,500),
                                      new SqlParameter("@OrderBy",SqlDbType.NVarChar,500),
                                      new SqlParameter("@PageIndex",SqlDbType.Int,4),
                                      new SqlParameter("@PageSize",SqlDbType.Int,4),
                                      new SqlParameter("@IsCount",SqlDbType.Bit,1)
                                  };
           paras[0].Value = hoggeryId;
           paras[1].Value = whereStr;
           paras[2].Value = orderByStr;
           paras[3].Value = currentPage;
           paras[4].Value = pageSize;
           paras[5].Value = 1;
           try
           {
               DataSet ds = DbHelperSQL.RunProcedure(procName, paras);
               if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
               {
                   string str= ds.Tables[0].Rows[0][0].ToString();
                   int.TryParse(str, out rCount);
                   return ds;
               }
             return null;
           }catch(Exception ex){
               throw new Exception("数据库连接错误，请稍后再试。");
           }
            
        }

       public DataSet GetSowBasicInfo(Guid sowID, int hoggeryId) {
           try
           {
               string procName = Common.PubProcedures.GetSowBasicInfo;
               SqlParameter[] paras = { 
                                      new SqlParameter("@HoggeryID", SqlDbType.Int, 4),
                                      new SqlParameter("@SowId",SqlDbType.UniqueIdentifier,16)
                                  };
               paras[0].Value = hoggeryId;
               paras[1].Value = sowID;
               DataSet ds = DbHelperSQL.RunProcedure(procName, paras);
               return ds;
           }catch(Exception ex){
               throw new Exception("数据库连接错误，请稍后再试。");
           }
       }

		#endregion  ExtensionMethod


       
    }
}

